package intranet.models;
import java.sql.*;
public class ArticleModel {
	Connection DBConnection=null;
	public ArticleModel(){
		DatabaseModel DBModel=new DatabaseModel();
		DBConnection=DBModel.GetConnection();
	}
	
	public ResultSet SelectArticle(int Article_ID){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql=" SELECT ARTICLE_ID,ARTICLE_TITLE,DBMS_LOB.SUBSTR(ARTICLE_DESC,DBMS_LOB.GETLENGTH(ARTICLE_DESC),1) AS ARTICLE_DESC,PREPARED_BY,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM ARTICLES WHERE ARTICLE_ID="+Article_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	public ResultSet SelectTopArticle(){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT ARTICLE_ID,ARTICLE_TITLE,DBMS_LOB.SUBSTR(ARTICLE_DESC,DBMS_LOB.GETLENGTH(ARTICLE_DESC),1) AS ARTICLE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM ARTICLES ORDER BY VIEW_NUMBER DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet AritcleFile(int Article_ID){
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT * FROM ARTICLE_FILES WHERE ARTICLE_ID="+Article_ID;
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public int RowCount(ResultSet rs) throws SQLException{
		int result=0;
		while(rs.next()){
			result++;
		}
		return result;
	}
	public ResultSet SelectOtherArticle() throws SQLException{
		ResultSet result=null;
		int i=0;
		int RowCount=this.RowCount(this.SelectTopArticle());
		int EndIndex=0;
		String Condition=(RowCount>0)?" WHERE ":"";
		/* Generate CONDITIONAL STRING TO AVOID SELECTING THE SAME COURSE AS TOP COURSE */
		EndIndex=(RowCount>=5)?4:RowCount-1;
		ResultSet TopArticle=this.SelectTopArticle();
		while(TopArticle.next()){
			if(i<EndIndex){
				Condition+="ARTICLE_ID!="+TopArticle.getInt("ARTICLE_ID")+" AND ";
			}
			if(i==EndIndex){
				Condition+="ARTICLE_ID!="+TopArticle.getInt("ARTICLE_ID");
			}
			i++;
		}

		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT ARTICLE_ID,ARTICLE_TITLE,DBMS_LOB.SUBSTR(ARTICLE_DESC,DBMS_LOB.GETLENGTH(ARTICLE_DESC),1) AS ARTICLE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM ARTICLES "+Condition+" ORDER BY DATE_POST DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}	
	
	public ResultSet SelectMoreArticle() throws SQLException{
		ResultSet result=null;
		try{
			Statement ConStatement=DBConnection.createStatement();
			String Sql="SELECT ARTICLE_ID,ARTICLE_TITLE,DBMS_LOB.SUBSTR(ARTICLE_DESC,DBMS_LOB.GETLENGTH(ARTICLE_DESC),1) AS ARTICLE_DESC,VIEW_NUMBER,TO_CHAR(DATE_POST,'DD/MM/YYYY') AS DATE_POST,USER_POST FROM ARTICLES ORDER BY DATE_POST DESC";
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
	public ResultSet SearchArticle(int cat_id,String keywords){
		ResultSet result=null;
		String Sql="";
		if(cat_id==0){		
			if(keywords!=""){
				Sql="SELECT A.ARTICLE_ID,A.ARTICLE_TITLE,DBMS_LOB.SUBSTR(A.ARTICLE_DESC,DBMS_LOB.GETLENGTH(A.ARTICLE_DESC),1) AS ARTICLE_DESC,A.VIEW_NUMBER,TO_CHAR(A.DATE_POST,'DD/MM/YYYY') AS DATE_POST,A.USER_POST FROM ARTICLES A LEFT JOIN ";
				Sql+="ARTICLE_KEYWORDS K ON A.ARTICLE_ID=K.ARTICLE_ID WHERE lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(A.ARTICLE_TITLE) LIKE'%"+keywords.toLowerCase()+"%' ORDER BY A.VIEW_NUMBER DESC";
			}else{
				Sql="SELECT A.ARTICLE_ID,A.ARTICLE_TITLE,DBMS_LOB.SUBSTR(A.ARTICLE_DESC,DBMS_LOB.GETLENGTH(A.ARTICLE_DESC),1) AS ARTICLE_DESC,A.VIEW_NUMBER,TO_CHAR(A.DATE_POST,'DD/MM/YYYY') AS DATE_POST,A.USER_POST FROM ARTICLES A ";
				Sql+="ORDER BY A.VIEW_NUMBER DESC";
			}	
			
		}else{
			if(keywords!=""){
				Sql="SELECT A.ARTICLE_ID,A.ARTICLE_TITLE,DBMS_LOB.SUBSTR(A.ARTICLE_DESC,DBMS_LOB.GETLENGTH(A.ARTICLE_DESC),1) AS ARTICLE_DESC,A.VIEW_NUMBER,TO_CHAR(A.DATE_POST,'DD/MM/YYYY') AS DATE_POST,A.USER_POST FROM ARTICLES A LEFT JOIN ";
				Sql+="ARTICLE_KEYWORDS K ON A.ARTICLE_ID=K.ARTICLE_ID WHERE (lower(K.KEYWORD) LIKE'%"+keywords.toLowerCase()+"%' OR lower(A.ARTICLE_TITLE) LIKE'%"+keywords.toLowerCase()+"%') AND A.CATEGORY_ID="+cat_id+" ORDER BY A.VIEW_NUMBER DESC";
			}else{
				Sql="SELECT A.ARTICLE_ID,A.ARTICLE_TITLE,DBMS_LOB.SUBSTR(A.ARTICLE_DESC,DBMS_LOB.GETLENGTH(A.ARTICLE_DESC),1) AS ARTICLE_DESC,A.VIEW_NUMBER,TO_CHAR(A.DATE_POST,'DD/MM/YYYY') AS DATE_POST,A.USER_POST FROM ARTICLES A ";
				Sql+="WHERE A.CATEGORY_ID="+cat_id+" ORDER BY A.VIEW_NUMBER DESC";
			}	
		}
		try{
			Statement ConStatement=DBConnection.createStatement();
			result=ConStatement.executeQuery(Sql);
		}catch(Exception e){
			e.getLocalizedMessage();
		}
		return result;
	}
}
